# Целесообразность внешних ключей на регистровых таблицах ## Введение В нашей ERP-системе PostgreSQL используется как основное хранилище данных. База данных содержит тысячи таблиц, включая как основные бизнес-сущности с полноценной реляционной моделью, так и различные технические структуры хранения данных. Помимо классических регистровых таблиц в системе существуют и другие технические структуры, которые формально регистрами не являются, но по своей природе выполняют схожую роль. Это таблицы, содержащие **копии или проекции бизнес-данных**, предназначенные для унификации API, упрощения запросов или реализации общих интерфейсов. Примером такой структуры является класс **складской операции** `STK_Operation`, а также все миксины. Данные в подобных таблицах не являются первичными: они формируются на основе других сущностей системы и используются как технический слой представления или агрегации данных. Общая особенность всех таких таблиц заключается в том, что данные в них: - не вводятся пользователем напрямую; - формируются из уже проверенных источников; - не являются источником истины. Поэтому с точки зрения нагрузки на базу данных регистровые таблицы и подобные технические структуры должны рассматриваться одинаково. Применение к ним внешних ключей и большого количества индексов требует особенно аккуратного и осознанного подхода, поскольку повторная проверка целостности на этом уровне, как правило, не добавляет новой ценности, но создаёт заметную эксплуатационную нагрузку. ## Внешние ключи на регистровых таблицах и их реальная цена Часто регистровые таблицы создаются по аналогии с основными сущностями — с внешними ключами на справочники и связанные таблицы. Формально это выглядит корректно, но на практике такие внешние ключи почти никогда не добавляют новой ценности. Данные, попадающие в регистр, **уже прошли проверку ссылочной целостности** в исходных таблицах. Повторная проверка на уровне регистра не защищает от новых ошибок, но создаёт дополнительную нагрузку на базу данных. В PostgreSQL внешний ключ — это не просто декларативное ограничение. При каждой вставке или обновлении строки в дочерней таблице выполняется служебный запрос, проверяющий наличие соответствующей строки в родительской таблице: ```sql SELECT 1 FROM ONLY "public"."bs_depowner" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x ``` Этот запрос выполняется **для каждой вставляемой строки**. Для регистров, где характерны массовые операции (`INSERT … SELECT`, пакетные загрузки, перерасчёты), это означает огромное количество служебных запросов, которые хорошо видны в отчётах по базе данных и создают ощутимую нагрузку на CPU и систему блокировок. ## Почему внешний ключ всегда приводит к появлению индекса Если в таблице существует внешний ключ, на соответствующее поле **обязан быть индекс**. Это требование связано с операциями удаления и обновления строк в родительской таблице. При удалении строки из справочника PostgreSQL обязан проверить, что в дочерних таблицах нет ссылающихся строк. Без индекса на поле внешнего ключа такая проверка выполняется через последовательное сканирование всей дочерней таблицы. Для крупных регистров это приводит к длительным блокировкам и резкой деградации операций `DELETE` и `UPDATE`. Поэтому наличие внешнего ключа практически всегда означает наличие индекса, который постоянно обновляется при записи данных в регистр. ## Стоимость индексов при вставке данных Каждый индекс — это отдельная структура данных, которая должна быть обновлена при вставке строки. Если таблица не имеет индексов, вставка относительно дешёвая и сводится к записи строки в heap и генерации WAL. Добавление индексов делает вставку существенно дороже. Практика эксплуатации показывает следующие порядки величин: - один btree-индекс замедляет вставку примерно на 20–40 %; - три–пять индексов увеличивают стоимость вставки в 2–3 раза; - десять и более индексов могут замедлять вставку в 4–6 раз и более, особенно при конкурентной нагрузке. Эта цена платится **всегда**, независимо от того, используется индекс в запросах или нет. ## Анализ использования индексов Для анализа использования индексов применяются представления `pg_stat_user_indexes` и `pg_stat_all_indexes`. Основной показатель — `idx_scan`, количество сканирований индекса. Если индекс имеет `idx_scan = 0` за продолжительный период времени, он является кандидатом на удаление. Однако корректность такого вывода напрямую зависит от момента сброса статистики. ## Сброс статистики и интерпретация данных PostgreSQL хранит точную дату и время последнего сброса статистики по базе данных. Эта информация доступна в представлении `pg_stat_database`: ```sql SELECT datname, stats_reset FROM pg_stat_database; ``` Все счётчики в `pg_stat_user_tables` и `pg_stat_user_indexes` интерпретируются **начиная с этого момента**. Отдельной даты сброса статистики для таблиц или индексов не существует. Если статистика была сброшена недавно, низкие значения `idx_scan` не являются признаком неиспользуемости индекса — они лишь означают, что период наблюдения был слишком коротким. При необходимости начать контролируемый интервал наблюдения статистику можно сбросить вручную: ```sql SELECT pg_stat_reset(); ``` ## Ограничения статистики: bitmap-сканирования Индексы, используемые в `Bitmap Index Scan`, могут иметь заниженные значения `idx_scan` или выглядеть как неиспользуемые. Это особенность учёта статистики, которую необходимо учитывать при анализе кандидатов на удаление. ## Пример запроса для поиска кандидатов на удаление индексов Запрос помогает выявить **кандидатов на удаление индексов** на регистровых и производных таблицах. Он ориентирован на поиск **одноколоночных индексов**, которые: - ни разу не использовались (`idx_scan = 0`); - не имеют зафиксированного времени последнего использования; - не являются первичными ключами; - не являются уникальными индексами; - не поддерживают внешние ключи. ```{attention} **Запрос не является универсальным и требует фильтрации по таблицам.** Его цель — предоставить инженеру список кандидатов для анализа, а не готовое решение для автоматического удаления. ``` ### SQL-запрос ```sql WITH index_usage AS ( SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan, s.last_idx_scan, s.indexrelid, s.relid AS table_relid, pg_relation_size(s.indexrelid) AS index_size FROM pg_stat_user_indexes s ), single_column_indexes AS ( SELECT i.indexrelid, i.indrelid AS table_relid, i.indkey[0] AS attnum, i.indisprimary, i.indisunique FROM pg_index i WHERE i.indnkeyatts = 1 AND i.indkey[0] > 0 ), fk_columns AS ( SELECT con.conrelid AS table_relid, unnest(con.conkey) AS attnum FROM pg_constraint con WHERE con.contype = 'f' ), table_estimates AS ( SELECT c.oid AS table_relid, c.reltuples::bigint AS approx_table_rows FROM pg_class c WHERE c.relkind = 'r' ) SELECT iu.schemaname, iu.table_name, te.approx_table_rows, iu.index_name, iu.idx_scan, iu.last_idx_scan, pg_size_pretty(iu.index_size) AS index_size, sci.indisprimary AS is_primary_key, sci.indisunique AS is_unique_index, EXISTS ( SELECT 1 FROM fk_columns fk WHERE fk.table_relid = sci.table_relid AND fk.attnum = sci.attnum ) AS is_foreign_key_index, ( iu.idx_scan = 0 AND iu.last_idx_scan IS NULL AND NOT sci.indisprimary AND NOT sci.indisunique AND NOT EXISTS ( SELECT 1 FROM fk_columns fk WHERE fk.table_relid = sci.table_relid AND fk.attnum = sci.attnum ) ) AS candidate_for_drop FROM index_usage iu JOIN single_column_indexes sci ON sci.indexrelid = iu.indexrelid LEFT JOIN table_estimates te ON te.table_relid = iu.table_relid ORDER BY iu.table_name, iu.index_name; ``` ### Как использовать результат Результат содержит признак `candidate_for_drop`, но его **нельзя трактовать как автоматическую рекомендацию**. Перед использованием запроса необходимо: 1. **Добавить фильтр по таблицам** Выполняйте запрос только для: - регистровых таблиц; - производных технических структур (миксины, проекции, API-таблицы); - таблиц, не являющихся источниками истины. 2. **Проверить момент сброса статистики** Все значения `idx_scan` и `last_idx_scan` актуальны с момента последнего сброса в `pg_stat_database`. 3. **Учитывать bitmap-сканирования** Индексы в `Bitmap Index Scan` могут иметь `idx_scan = 0`, но при этом реально использоваться. 4. **Оценить контекст использования таблицы** Решение об удалении принимайте только при понимании назначения таблицы и всех сценариев её использования. ## Генерация/отключение/удаление индексов и внешних ключей ### Генерация индексов и внешних ключей Все настройки генерации индексов и внешних ключей задаются в **метаданных (ODM)** конкретных классов. По умолчанию система автоматически создаёт **внешние ключи и индексы для ссылочных атрибутов**. Для всех остальных атрибутов внешний ключ и индекс генерируется только при явном указании дополнительных условий в коде метаданных. ### Отключение генерации внешних ключей для интеграции со сторонними системами Для параметров `jidext_dz` и `sidext_dz`, используемых при взаимодействии со внешними базами данных, по умолчанию **всегда генерируются внешние ключи и индексы** для всех таблиц. Чтобы отключить автогенерацию, установите параметр `externalId.Enabled` в метаданных ODM: ```xml ``` Этот параметр отключает создание внешних ключей и индексов для всех атрибутов, связанных с `jidext_dz` и `sidext_dz`. ```{attention} Если индексы `jidext_dz` и `sidext_dz` уже созданы в БД, их нужно удалять через миграцию схемы БД (`dbSchema`/`dbData`) ``` ### Отключение индексов и внешних ключей на ссылочных атрибутах Для ссылочных атрибутов внутри системы (например, связи между регистрами и справочниками) автогенерацию индекса и внешнего ключа можно отключить в метаданных ODM: ```xml ``` Эта настройка: - отключает создание **внешнего ключа** (`foreignKeyType="disable"`); - отключает создание **индекса** (`indexType="noIndex"`). ```{attention} Параметр `indexType="noIndex"` предотвращает только **создание новых индексов**. Если индекс уже существует, его необходимо удалить вручную через миграцию схемы БД (`dbSchema`/`dbData`). Существующие внешние ключи удаляются автоматически при применении `foreignKeyType="disable"`. ``` Генерация схемы базы данных на основе этих настроек выполняется автоматически при сборке модуля. ### Рекомендации при создании регистровых таблиц При проектировании новых регистровых таблиц соблюдайте следующие правила: - Создавайте документы с типом `supertype="journal"` или `supertype="setting"`. Эти типы являются наиболее лёгковесными и не содержат избыточной бизнес-логики. - Удаляйте поле `gid`, если оно было добавлено автоматически. Поле `gid` создаёт дополнительный индекс и увеличивает объём данных. ### Удаление индексов и внешних ключей Удаление индексов и внешних ключей выполняется **в рамках релиза модуля**. Все изменения схемы базы данных должны быть оформлены в виде SQL-операторов в миграционных скриптах. Конкурентное удаление индексов (`DROP INDEX CONCURRENTLY`) **не требуется**, поскольку операция выполняется при остановленной системе и отключённых пользователях. ### Удаление внешнего ключа ```sql ALTER TABLE public.reg_table DROP CONSTRAINT fk_reg_table_ref; ``` Перед удалением внешнего ключа убедитесь, что: - Он не используется для обеспечения критичной логики. - Данные в регистре формируются только из проверенных источников. ### Удаление индекса ```sql DROP INDEX public.idx_reg_table_ref_id; ``` Индекс удаляется обычной командой `DROP INDEX`, без конкурентного режима. ### Выводы Регистровые таблицы в нашей ERP-системе предназначены для хранения уже проверенных данных. Внешние ключи и связанные с ними индексы на этом уровне часто не добавляют новых гарантий, но стабильно увеличивают стоимость записи и создают дополнительную нагрузку на базу данных. Осознанное управление схемой регистров, основанное на архитектурной роли таблиц и анализе реального использования, позволяет снизить нагрузку на БД без потери корректности данных.